package com.example.controller;


import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.enums.WriteDirectionEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.write.metadata.fill.FillWrapper;
import com.example.domain.Columns;
import com.example.domain.TableConstraintsAndColumn;
import com.example.domain.Tables;


import com.example.service.ColumnsService;
import com.example.service.TableConstraintsService;
import com.example.service.TablesService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.ClassPathResource;
import org.springframework.web.bind.annotation.*;

import javax.annotation.Resource;
import javax.xml.crypto.Data;
import javax.xml.transform.Result;
import java.io.*;
import java.sql.Array;
import java.util.*;


@RestController

@Api(tags = "tables的导出")
public class TablesController {
    /**
     * 服务对象
     */
    @Resource
    private TablesService tablesService;
    @Resource
    private ColumnsService columnsService;
    @Resource
    TableConstraintsService tableConstraintsService;


    @ApiOperation(value = "查询column表中表结构")
    @GetMapping("/test")

    public String selectBySchema() throws IOException {
        int index = 1;
        String templateFile = "D:\\template.xlsx";
        String fileName = "D:\\tablesInfo" + System.currentTimeMillis() + ".xlsx";

        //  获得库中所有tables name
        List<String> tableName = tablesService.selectBySchemaDTO("shop").getTableName();
        // 获得tables comment
        List<String> tableComment = tablesService.selectBySchemaDTO("shop").getTableComment();
        // 获得 表结构 数据
        //List<Columns> columnsList = columnsService.selectBySchema("shop", fileName);

        // 要写入的字段集合
        Set<String> includeColumnFieldNames = new HashSet<>();
        //includeColumnFieldNames.add("tableName");
        includeColumnFieldNames.add("columnName");
        includeColumnFieldNames.add("columnType");
        includeColumnFieldNames.add("isNullable");
        includeColumnFieldNames.add("columnDefault");
        includeColumnFieldNames.add("columnComment");


        // 指定写入的文件
        ExcelWriter excelWriter = EasyExcel.write(fileName, Columns.class).build();


        //1 对list的数据单独写入一个表 循环
        for (String table : tableName) {
            //  得到每个sheet 的名字
            // 每次都要创建writeSheet 这里注意必须指定sheetNo 而且sheetName必须不一样
            WriteSheet writeSheet = EasyExcel.writerSheet(index, table).build();
            excelWriter.write(columnsService.selectBySchema("shop", table), writeSheet);
            index++;
        }

        excelWriter.finish();



       /*  这里注意 使用swagger 会导致各种问题，请直接用浏览器或者用postman
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

        EasyExcel.write(response.getOutputStream(), Tables.class).sheet("ceshi  ").doWrite(shop);*/
        return "写入完成 " + new Date(System.currentTimeMillis());
    }

    @GetMapping("/test/tem")
    public String ExportByTemplate() {

        try {
            int result =tablesService.generateTablesStructure();
            if (result == 1) {
                return "success! " + new Date(System.currentTimeMillis());
            }

        } catch (IOException e) {
            e.printStackTrace();
        }
        return "false";
    }

}
